import os
import json
import sqlite3
from datetime import datetime
import logging

logger = logging.getLogger("log/weather_database")

# 数据目录
DATA_DIR = "weather_data"
DB_FILE = "weather.db"


def init_database():
    """初始化SQLite数据库"""
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    # 创建天气数据表
    cursor.execute('''  
    CREATE TABLE IF NOT EXISTS weather (  
        id INTEGER PRIMARY KEY AUTOINCREMENT,  
        city TEXT NOT NULL,  
        date TEXT NOT NULL,  
        high_temp TEXT,  
        low_temp TEXT,  
        weather TEXT,  
        wind_direction TEXT,  
        wind_force TEXT,  
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
        UNIQUE(city, date)  
    )  
    ''')

    # 创建城市表
    cursor.execute('''  
    CREATE TABLE IF NOT EXISTS cities (  
        id INTEGER PRIMARY KEY AUTOINCREMENT,  
        name TEXT NOT NULL,  
        code TEXT NOT NULL UNIQUE,  
        province TEXT,  
        last_updated TIMESTAMP  
    )  
    ''')

    conn.commit()
    conn.close()

    logger.info("Database initialized")


def load_json_to_db():
    """将JSON文件数据导入到数据库"""
    if not os.path.exists(DATA_DIR):
        logger.error(f"Data directory {DATA_DIR} not found")
        return False

    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    total_records = 0

    # 遍历所有城市目录
    for city in os.listdir(DATA_DIR):
        city_dir = os.path.join(DATA_DIR, city)

        if not os.path.isdir(city_dir):
            continue

        city_records = 0

        # 遍历城市下的所有JSON文件
        for filename in os.listdir(city_dir):
            if not filename.endswith('.json'):
                continue

            file_path = os.path.join(city_dir, filename)

            try:
                with open(file_path, 'r', encoding='utf-8') as f:
                    data = json.load(f)

                    # 插入数据
                for record in data:
                    try:
                        cursor.execute(
                            "INSERT OR REPLACE INTO weather (city, date, high_temp, low_temp, weather, wind_direction, wind_force) "
                            "VALUES (?, ?, ?, ?, ?, ?, ?)",
                            (
                                city,
                                record['date'],
                                record.get('high_temp', ''),
                                record.get('low_temp', ''),
                                record.get('weather', ''),
                                record.get('wind_direction', ''),
                                record.get('wind_force', '')
                            )
                        )
                        city_records += 1
                    except Exception as e:
                        logger.error(f"Error inserting record: {e}")

            except Exception as e:
                logger.error(f"Error loading file {file_path}: {e}")

                # 更新城市表
        cursor.execute(
            "INSERT OR REPLACE INTO cities (name, code, last_updated) VALUES (?, ?, ?)",
            (city, city, datetime.now().isoformat())
        )

        logger.info(f"Loaded {city_records} records for {city}")
        total_records += city_records

    conn.commit()
    conn.close()

    logger.info(f"Total {total_records} records loaded into database")
    return total_records


def get_city_weather(city, date=None):
    """获取指定城市的天气数据"""
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    if date:
        # 获取特定日期的数据
        cursor.execute(
            "SELECT * FROM weather WHERE city = ? AND date = ?",
            (city, date)
        )
        row = cursor.fetchone()
        conn.close()

        if row:
            return dict(row)
        return None
    else:
        # 获取最近的数据
        cursor.execute(
            "SELECT * FROM weather WHERE city = ? ORDER BY date DESC LIMIT 30",
            (city,)
        )
        rows = cursor.fetchall()
        conn.close()

        return [dict(row) for row in rows]


def get_available_cities():
    """获取可用的城市列表"""
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM cities ORDER BY name")
    rows = cursor.fetchall()
    conn.close()

    return [dict(row) for row in rows]


if __name__ == "__main__":
    # 初始化数据库并导入数据
    init_database()
    record_count = load_json_to_db()
    print(f"Loaded {record_count} weather records into database")